import os
import configparser
import pymysql
from datetime import datetime

aspect_weights = {
    # 'finance': 0.3,
    # 'public_opinion': 0.25,
    # 'transportation_population': 0.2,
    # 'material_reserve': 0.25,
}

city_weights = {
    # '贵阳市': 0.25,
    # '遵义市': 0.15,
    # '六盘水市': 0.05,
    # '安顺市': 0.05,
    # '毕节市': 0.1,
    # '铜仁市': 0.1,
    # '黔东南苗族侗族自治州': 0.1,
    # '黔南布依族苗族自治州': 0.1,
    # '黔西南布依族苗族自治州': 0.1,
}

# 获取当前脚本所在目录的绝对路径
script_dir = os.path.dirname(os.path.abspath(__file__))

config = configparser.ConfigParser()
config.read(os.path.join(script_dir, 'config.ini'))

url_section = config['MYSQL']
mysql_host = url_section['mysql_host']
mysql_port = url_section['mysql_port']
mysql_user = url_section['mysql_user']
mysql_password = url_section['mysql_password']
mysql_database = url_section['mysql_database']
default_area_code = url_section['default_area_code']

# 连接到数据库
conn = pymysql.connect(host=mysql_host,
                       port=int(mysql_port),
                       user=mysql_user,
                       password=mysql_password,
                       database=mysql_database
                       )
cursor = conn.cursor()


# 初始化一个空列表来存储所有区域

def find_all_descendants_titles(cursor, start_id):
    """
    使用广度优先搜索（BFS）查找给定起始id的所有子孙区域的title。

    :param cursor: MySQL游标对象
    :param start_id: 起始区域的id
    :return: 一个包含所有子孙区域title的列表
    """
    descendants_titles = []  # 用于存储已找到的子孙区域title
    queue = [(start_id,)]  # 初始化队列，将起始id（作为元组）放入队列，以便后续扩展

    while queue:
        current_id, = queue.pop(0)  # 从队列中取出一个id（作为元组，所以需要解包）

        # 查询当前id的所有直接子区域及其title
        try:
            cursor.execute("SELECT id, title FROM region WHERE parent_id = %s", (current_id,))
            subregions = cursor.fetchall()  # 获取查询结果

            # 将每个子区域的title添加到结果列表中
            for child_id, title in subregions:
                descendants_titles.append(title)
        except Error as e:
            print(f"Error querying descendants for id {current_id}: {e}")
            # 可以选择在这里记录错误、退出函数或继续处理其他id

    return descendants_titles


def calculate_city_pressure_index(city_indices, aspect_weights):
    """
    计算单个城市的承压指数。
    """
    city_pressure_index = 0
    for aspect, weight in aspect_weights.items():
        score = city_indices.get(aspect, 0)  # 获取该城市在该方面的指数
        city_pressure_index += score * weight
    return city_pressure_index


def calculate_aspect_index(city_indices, city_weights, aspect):
    """
    计算贵州省在某一方面（金融、舆情、交通人口、物资储备）的加权平均指数。
    """
    total_weighted_score = 0
    total_weight = 0

    for city, indices in city_indices.items():
        score = indices.get(aspect, 0)  # 获取该城市在该方面的指数
        weight = city_weights.get(city, 0)  # 获取该城市的权重
        total_weighted_score += score * weight
        total_weight += weight

    if total_weight == 0:
        return 0

    return total_weighted_score / total_weight


def calculate_pressure_indices(city_indices, city_weights, aspect_weights):
    """
    计算贵州省整体的承压指数、各方面指数以及各城市的承压指数。
    """
    # 计算各方面的指数
    aspect_indices = {
        'finance': calculate_aspect_index(city_indices, city_weights, 'finance'),
        'public_opinion': calculate_aspect_index(city_indices, city_weights, 'public_opinion'),
        'transportation_population': calculate_aspect_index(city_indices, city_weights, 'transportation_population'),
        'material_reserve': calculate_aspect_index(city_indices, city_weights, 'material_reserve'),
    }

    # 计算贵州省整体的承压指数
    province_pressure_index = (aspect_indices['finance'] * aspect_weights['finance'] +
                               aspect_indices['public_opinion'] * aspect_weights['public_opinion'] +
                               aspect_indices['transportation_population'] * aspect_weights[
                                   'transportation_population'] +
                               aspect_indices['material_reserve'] * aspect_weights['material_reserve'])

    # 计算各城市的承压指数
    city_pressure_indices = {}
    for city, indices in city_indices.items():
        city_pressure_indices[city] = calculate_city_pressure_index(indices, aspect_weights)

    return province_pressure_index, aspect_indices, city_pressure_indices


# 示例数据
city_indices = {
    # '贵阳市': {'finance': 80, 'public_opinion': 85, 'transportation_population': 90, 'material_reserve': 88},
    # '遵义市': {'finance': 75, 'public_opinion': 80, 'transportation_population': 82, 'material_reserve': 85},
    # '六盘水市': {'finance': 63, 'public_opinion': 86, 'transportation_population': 85, 'material_reserve': 75},
    # '安顺市': {'finance': 35, 'public_opinion': 15, 'transportation_population': 26, 'material_reserve': 15},
    # '毕节市': {'finance': 25, 'public_opinion': 26, 'transportation_population': 62, 'material_reserve': 16},
    # '铜仁市': {'finance': 75, 'public_opinion': 65, 'transportation_population': 63, 'material_reserve': 85},
    # '黔东南苗族侗族自治州': {'finance': 85, 'public_opinion': 40, 'transportation_population': 82, 'material_reserve': 86},
    # '黔南布依族苗族自治州': {'finance': 69, 'public_opinion': 80, 'transportation_population': 69, 'material_reserve': 58},
    # '黔西南布依族苗族自治州': {'finance': 30, 'public_opinion': 47, 'transportation_population': 58, 'material_reserve': 94},
    # 其他州市的数据...
}


# 定义一个函数来将元组转换为字典

def tuple_to_dict(tuple_row, column_names):
    """
    将元组转换为字典。
    :param tuple_row: 要转换的元组行。
    :param column_names: 列名的列表，顺序应与元组中的值相匹配。
    :return: 对应的字典。
    """
    return dict(zip(column_names, tuple_row))

#获取城市权重
def get_city_weights():
    # 查询数据
    query = "SELECT * FROM city_index_weight"
    cursor.execute(query)

    # 获取所有结果
    tuples_list = cursor.fetchall()

    # 打印结果
    # 使用字典推导式来转换元组列表
    global city_weights  # 声明使用全局变量
    city_weights = {city: float(weight) for _, city, weight in tuples_list}

    # 输出结果以验证
    print(city_weights)

#获取城市指数
def get_city_indices(current_date):
    # 查询数据
    query = "SELECT * FROM index_area WHERE date = %s"
    cursor.execute(query, current_date)

    # 获取所有结果
    tuples_list = cursor.fetchall()

    # 打印结果
    # 使用字典推导式来转换元组列表
    global city_indices  # 声明使用全局变量
    # 执行转换
    city_indices = convert_data(tuples_list)

    # 输出结果以验证
    print(city_indices)


# 转换函数
def convert_data(raw_data):
    city_indices = {}

    for item in raw_data:
        # 解包数据（索引位置说明）
        # 0: ID, 1: 城市名, 2: 日期,
        # 3: 未知指标（根据需求忽略）,
        # 4: 金融指标, 5: 舆论指标,
        # 6: 交通人口指标, 7: 物资储备指标
        _, city_name, _, _, finance, public_opinion, transportation_population, material_reserve = item

        city_indices[city_name] = {
            'finance': finance,
            'public_opinion': public_opinion,
            'transportation_population': transportation_population,
            'material_reserve': material_reserve
        }

    return city_indices




def get_pressure_index_weights():
    # 查询数据
    query = "select index_id,name,weight FROM pressure_index_weight WHERE parent_id=1"
    cursor.execute(query)

    # 获取所有结果
    tuples_list = cursor.fetchall()

    # 打印结果
    # 使用字典推导式来转换元组列表
    global aspect_weights  # 声明使用全局变量
    aspect_weights = {name: float(weight / 100) for _, name, weight, in tuples_list}

    # 输出结果以验证
    print(aspect_weights)


# 主程序
def insert_guizhou_province_index(current_date,province_pressure_index,indices):
    query = """
       SELECT `index` FROM index_trend
       WHERE date = %s
       """
    cursor.execute(query, current_date)
    result = cursor.fetchone()

    if result is None:
        # 插入新数据到 index_trend
        insert_query = """
                   INSERT INTO index_trend (date, `index`, finance, public_opinion, transportation_population, material_reserve)
                   VALUES (%s, %s, %s, %s, %s, %s)
                   """
        cursor.execute(insert_query, (
        current_date, province_pressure_index, indices.get('finance'), indices.get('public_opinion'), indices.get('transportation_population'),
        indices.get('material_reserve')))
        conn.commit()
        print(
            f"Inserted new index {province_pressure_index} new finance {indices.get('finance')} new public_opinion {indices.get('public_opinion')} new transportation_population {indices.get('transportation_population')} new material_reserve {indices.get('material_reserve')} on {current_date}")
    else:
        print(f"Existing index {result[0]} on {current_date}")
    pass


def insert_guizhou_citys_index(current_date, indices):
    try:
        # 开启事务
        conn.begin()

        # 批量更新操作
        for city, index_value in indices.items():
            update_query = """
               UPDATE index_area 
               SET `index` = %s
               WHERE date = %s AND city = %s
               """
            cursor.execute(update_query, (index_value, current_date, city))

        # 提交事务
        conn.commit()
        print(f"成功更新 {len(indices)} 条记录")
    except Error as e:
        # 回滚事务
        conn.rollback()
        print(f"数据库错误: {str(e)}")
    pass


if __name__ == "__main__":
    adcode_to_query = '24'
    current_date = datetime.now().strftime('%Y-%m-%d')

    all_regions = find_all_descendants_titles(cursor, adcode_to_query)
    # check_or_insert_index_area(all_regions, current_date)

    get_city_weights()
    get_city_indices(current_date)
    get_pressure_index_weights()

    # 计算贵州省承压指数、各方面指数以及各城市的承压指数
    province_pressure_index, aspect_indices, city_pressure_indices = calculate_pressure_indices(city_indices,
                                                                                                city_weights,
                                                                                                aspect_weights)
    # 输出结果
    print(f"贵州省承压指数: {province_pressure_index:.2f}")

    print("各方面指数:")
    for aspect, index in aspect_indices.items():
        print(f"{aspect}: {index:.2f}")

    print("\n各城市承压指数:")
    for city, index in city_pressure_indices.items():
        print(f"{city}: {index:.2f}")

    #写入贵州省承压指数
    insert_guizhou_province_index(current_date, province_pressure_index, aspect_indices)

    #写入贵州省各地级市承压指数
    insert_guizhou_citys_index(current_date, city_pressure_indices)

    # 关闭数据库连接
    cursor.close()
    conn.close()
